//File: QueryModel.java

package model;

import client.ClientView;
import java.sql.*;
import java.util.ArrayList;
import javax.swing.event.EventListenerList;

/**
 * Qenerates and sends queries to the database to be processed.
 *  
 * @author Tobias Robinson
 */
public class QueryModel {
    private PreparedStatement ps = null;
    private EventListenerList listenerList = new EventListenerList();
    private DBConnection dbc = DBConnection.getInstance();

    private static ArrayList<String> flights = new ArrayList<String>();

    public QueryModel() {        
        //empty so other classes can access functions
    }
    
    /**
     * Queries the database for all flight info. The ResultSet is
     * scroll insensitive and read only. 
     * 
     * @return returns either the ResultSet or Null if there is an error
     */    
    public ResultSet showFlight() {
        try {
            ps = dbc.getConnection().prepareStatement("SELECT * FROM FLIGHT ORDER BY `FLIGHT`.`DATE` ASC",
				      ResultSet.TYPE_SCROLL_INSENSITIVE,
				      ResultSet.CONCUR_READ_ONLY);
                      //ResultSet.CONCUR_UPDATABLE);
            ResultSet rs = ps.executeQuery();
            return rs;
        } catch (SQLException ex) {
            ExceptionEvent event = new ExceptionEvent(this, ex.getMessage());
            fireExceptionGenerated(event);
            
            // no need to commit or rollback since it is only a query            
            return null;
        }
    }


    public ResultSet getFlightDates() {
        try {
            ps = dbc.getConnection().prepareStatement("SELECT DATE FROM FLIGHT",
				      ResultSet.TYPE_SCROLL_INSENSITIVE,
				      ResultSet.CONCUR_READ_ONLY);
            ResultSet rs = ps.executeQuery();
            return rs;
        } catch (SQLException ex) {
            ExceptionEvent event = new ExceptionEvent(this, ex.getMessage());
            fireExceptionGenerated(event);

            // no need to commit or rollback since it is only a query
            return null;
        }
    }

    public ResultSet editFlight() {
        try {
            ps = dbc.getConnection().prepareStatement("SELECT * FROM FLIGHT ORDER BY `FLIGHT`.`DATE` ASC",
				      ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
            ResultSet rs = ps.executeQuery();
            return rs;
        } catch (SQLException ex) {
            ExceptionEvent event = new ExceptionEvent(this, ex.getMessage());
            fireExceptionGenerated(event);

            // no need to commit or rollback since it is only a query
            return null;
        }
    }

    public ResultSet editAirport() {
        try {
            ps = dbc.getConnection().prepareStatement("SELECT * FROM AIRPORT ORDER BY `ID` ASC",
				      ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
            ResultSet rs = ps.executeQuery();
            return rs;
        } catch (SQLException ex) {
            ExceptionEvent event = new ExceptionEvent(this, ex.getMessage());
            fireExceptionGenerated(event);

            // no need to commit or rollback since it is only a query
            return null;
        }
    }

    public ResultSet getGST() {
        try {
            ps = dbc.getConnection().prepareStatement("SELECT * FROM GST ");
            ResultSet rs = ps.executeQuery();
            return rs;
        } catch (SQLException ex) {
            ExceptionEvent event = new ExceptionEvent(this, ex.getMessage());
            fireExceptionGenerated(event);

            // no need to commit or rollback since it is only a query
            return null;
        }
    }


    public ResultSet getAirports() {
         try {
            ps = dbc.getConnection().prepareStatement("SELECT NAME FROM AIRPORT ");
            ResultSet rs = ps.executeQuery();
            return rs;
        } catch (SQLException ex) {
            ExceptionEvent event = new ExceptionEvent(this, ex.getMessage());
            fireExceptionGenerated(event);

            // no need to commit or rollback since it is only a query
            return null;
        }
    }

    public ResultSet getAirportTax(String name) {
         try {
            ps = dbc.getConnection().prepareStatement("SELECT TAX FROM AIRPORT WHERE NAME = '" + name + "'");
            ResultSet rs = ps.executeQuery();
            return rs;
        } catch (SQLException ex) {
            ExceptionEvent event = new ExceptionEvent(this, ex.getMessage());
            fireExceptionGenerated(event);

            // no need to commit or rollback since it is only a query
            return null;
        }
    }

    public ResultSet getFlightPrice(String flightID) {
        try {
            ps = dbc.getConnection().prepareStatement("SELECT PRICE FROM FLIGHT WHERE ID= '" + flightID + "'");
            ResultSet rs = ps.executeQuery();
            return rs;
        } catch (SQLException ex) {
            ExceptionEvent event = new ExceptionEvent(this, ex.getMessage());
            fireExceptionGenerated(event);
            // no need to commit or rollback since it is only a query
            return null;
        }
    }

    public ResultSet getFlights() {
        try {
            ps = dbc.getConnection().prepareStatement("SELECT ID FROM FLIGHT ");
            ResultSet rs = ps.executeQuery();
            return rs;
        } catch (SQLException ex) {
            ExceptionEvent event = new ExceptionEvent(this, ex.getMessage());
            fireExceptionGenerated(event);

            // no need to commit or rollback since it is only a query
            return null;
        }
    }

    public ResultSet searchFlight(String origin, String destination, String date) {
        try {
            ps = dbc.getConnection().prepareStatement("SELECT * FROM FLIGHT WHERE ORIGIN = '" + origin + "'and DESTINATION = '" + destination + "' and DATE = '" + date + "'");
            ResultSet rs = ps.executeQuery();
            return rs;
        } catch (SQLException ex) {
            ExceptionEvent event = new ExceptionEvent(this, ex.getMessage());
            fireExceptionGenerated(event);
            // no need to commit or rollback since it is only a query
            return null;
        }
    }


    public ResultSet getFlightOrigin(String id) {
         try {
            ps = dbc.getConnection().prepareStatement("SELECT ORIGIN FROM FLIGHT WHERE ID= '" + id + "'");
            ResultSet rs = ps.executeQuery();
            return rs;
        } catch (SQLException ex) {
            ExceptionEvent event = new ExceptionEvent(this, ex.getMessage());
            fireExceptionGenerated(event);

            // no need to commit or rollback since it is only a query
            return null;
        }
    }

    public ResultSet getFlightDestination(String id) {
         try {
            ps = dbc.getConnection().prepareStatement("SELECT DESTINATION FROM FLIGHT WHERE ID= '" + id + "'");
            ResultSet rs = ps.executeQuery();
            return rs;
        } catch (SQLException ex) {
            ExceptionEvent event = new ExceptionEvent(this, ex.getMessage());
            fireExceptionGenerated(event);

            // no need to commit or rollback since it is only a query
            return null;
        }
    }

    /**
     * Queries the database for user details. 
     * Matches username and password. 
     * 
     * @param user username to be check on database
     * @param pwd password of user to check on database
     * @return returns whether user details are correct
     */ 
    public ResultSet checkUserLogin (String user, String pwd, String access) {
        try {
            ps = dbc.getConnection().prepareStatement("SELECT USER,PASSWORD, ACCESS FROM LOGIN WHERE USER='"+ user + "' and PASSWORD = '"+ pwd + "' and ACCESS = '"+ access +"'");
            ResultSet rs = ps.executeQuery();            
            return rs;
        } catch (SQLException ex) {
            ClientView.printMessage(ex.getMessage());
            
            // no need to commit or rollback since it is only a query            
            return null;
        }
    }
    
    /**
     * Queries the database for name of a user. 
     * Matches username. Used so username is not replicated.
     * 
     * @param user username to be check on database
     * @return returns whether username already exists
     */
    public ResultSet registerInput(String user) {
        try {
            ps = dbc.getConnection().prepareStatement("SELECT * FROM LOGIN WHERE USER='"+ user +"'");
            ResultSet rs = ps.executeQuery();        
            return rs;
        } catch (SQLException ex) {
            ClientView.printMessage(ex.getMessage());            
            // no need to commit or rollback since it is only a query            
            return null;
        }        
    }
    
    /**
     * Queries the database for name of a user. 
     * Matches username. Used so username is not replicated.
     * 
     * @param user username to be check on database
     */
    public void createUser(String user, String pwd, String access) {
        try {            
            ps = dbc.getConnection().prepareStatement("INSERT INTO LOGIN(USER, PASSWORD, ACCESS) VALUES('" + user + "', '" + pwd + "', '" + access + "')");
            ps.executeUpdate();
            ClientView.printMessage("Account: " + user + " created with access: " + access + "\n");
        } catch (SQLException ex) {
            ClientView.printMessage(ex.getMessage());
        }
    }

    public void addAirport(String name, String tax) {
        try {
            ps = dbc.getConnection().prepareStatement("INSERT INTO AIRPORT(NAME, TAX) VALUES('" + name + "', '0." + tax + "')");
            ps.executeUpdate();
            ClientView.printMessage("Airport: " + name + " created with tax: " + tax + "\n");
        } catch (SQLException ex) {
            ClientView.printMessage(ex.getMessage());
        }
    }

    public void deleteAirport(String name) {
        try {
            ps = dbc.getConnection().prepareStatement("DELETE FROM AIRPORT WHERE NAME='" + name + "'");
            ps.executeUpdate();
            ClientView.printMessage("Airport : " + name + " removed\n");
        } catch (SQLException ex) {
            ClientView.printMessage(ex.getMessage());
        }
    }


    public void editUser(String user, String pwd2) {
        try {
            ps = dbc.getConnection().prepareStatement("UPDATE LOGIN SET PASSWORD = '" + pwd2 + "' WHERE USER='"+ user + "'");
            ps.executeUpdate();
            ClientView.printMessage("User: " + user + " password updated\n");
        } catch (SQLException ex) {
            ClientView.printMessage(ex.getMessage());
        }
    }
    
    public void editGST(String gst) {
        try {
            ps = dbc.getConnection().prepareStatement("UPDATE GST SET VALUE = '0." + gst + "'");
            ps.executeUpdate();
            ClientView.printMessage("GST set to: " + gst + "% \n");
        } catch (SQLException ex) {
            ClientView.printMessage(ex.getMessage());
        }
    }
    
    /**
     * Queries the database to delete a user. 
     * Matches username and password.
     * 
     * @param user username to be deleted
     * @param pwd password of user to be deleted
     */
    public void deleteUser(String user, String pwd, String access) {
        try {            
            ps = dbc.getConnection().prepareStatement("DELETE FROM LOGIN WHERE USER='"+ user + "' and PASSWORD = '"+ pwd + "' and ACCESS = '" + access + "'");
            ps.executeUpdate();
            ClientView.printMessage("User: " + user + " removed\n");
        } catch (SQLException ex) {
            ClientView.printMessage(ex.getMessage());
        }
    }

    public void recordPurchase(String flightID, String user, double flightPrice, double gst) {
    }

    public void updateFlight(String flightID) {
        double seats = getSeats(flightID);
        seats = seats - 1;
         try {
            ps = dbc.getConnection().prepareStatement("UPDATE FLIGHT SET SEATS = '" + seats + "' WHERE ID='"+ flightID + "'");
            ps.executeUpdate();
        } catch (SQLException ex) {
            ClientView.printMessage(ex.getMessage());
        }
    }

    public double getSeats(String flightID) {
        double seats = 0;
        try {
            ps = dbc.getConnection().prepareStatement("SELECT SEATS FROM FLIGHT WHERE ID= '" + flightID + "'");
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                seats = (rs.getDouble(1));            }

        } catch (SQLException ex) {
            ExceptionEvent event = new ExceptionEvent(this, ex.getMessage());
            fireExceptionGenerated(event);
            // no need to commit or rollback since it is only a query
            return 0;
        }
        return seats;
    }


    public ResultSet getDiscount() {
        try {
            ps = dbc.getConnection().prepareStatement("SELECT DISCOUNT FROM PROMOTION ");
            ResultSet rs = ps.executeQuery();
            return rs;
        } catch (SQLException ex) {
            ExceptionEvent event = new ExceptionEvent(this, ex.getMessage());
            fireExceptionGenerated(event);

            // no need to commit or rollback since it is only a query
            return null;
        }
    }

    public ResultSet getPromMin() {
        try {
            ps = dbc.getConnection().prepareStatement("SELECT TICKETS FROM PROMOTION ");
            ResultSet rs = ps.executeQuery();
            return rs;
        } catch (SQLException ex) {
            ExceptionEvent event = new ExceptionEvent(this, ex.getMessage());
            fireExceptionGenerated(event);

            // no need to commit or rollback since it is only a query
            return null;
        }
    }


    public void editPromotion(String discount, String tickets) {
        try {
            ps = dbc.getConnection().prepareStatement("UPDATE PROMOTION SET DISCOUNT = '0." + discount + "'");
            ps.executeUpdate();
            ps = dbc.getConnection().prepareStatement("UPDATE PROMOTION SET TICKETS = '" + tickets + "'");
            ps.executeUpdate();
            ClientView.printMessage("Discount set to: " + discount + "% when " + tickets + " tickets are purchsed. \n");
        } catch (SQLException ex) {
            ClientView.printMessage(ex.getMessage());
        }
    }

    public void createFlight(String origin, String destination, String date, String seats, String price) {
        try {
            ps = dbc.getConnection().prepareStatement("INSERT INTO FLIGHT(ORIGIN, DESTINATION, DATE, SEATS, PRICE) VALUES('" + origin + "', '" + destination + "', '" + date + "', '" + seats + "', ' " + price + "')");
            ps.executeUpdate();
            ClientView.printMessage("Flight from: " + origin + " To: " + destination + " on " + date + " created\n");
        } catch (SQLException ex) {
            ClientView.printMessage(ex.getMessage());
        }
    }

    public void deleteFlight(String id) {
        try {
            ps = dbc.getConnection().prepareStatement("DELETE FROM FLIGHT WHERE ID='"+ id + "'");
            ps.executeUpdate();
            ClientView.printMessage("Flight ID: " + id + " removed\n");
        } catch (SQLException ex) {
            ClientView.printMessage(ex.getMessage());
        }
    }
    
    /**
     * Retrieves a copy of the running connection. 
     *
     * @return returns a copy of the connection.
     */
    public Connection getConnection() {
        return dbc.getConnection();
    }
    
    /**
     * Allows members of this class to clean up after itself 
     * before it is garbage collected. It is called by the garbage collector.
     * 
     * @throws throws error
     */    
    @Override
    protected void finalize() throws Throwable {
        if (ps != null) {
            ps.close();
        }
        // finalize() must call super.finalize() as the last thing it does        
        super.finalize();
    }

    /**
     * Adds an exception listener to listen for exceptions
     * 
     * @param l ExceptionListener used for listening
     */ 
    public void addExceptionListener(ExceptionListener l) {
        listenerList.add(ExceptionListener.class, l);
    }

    /**
     * Removes an exception listener that listen for exceptions
     * 
     * @param l ExceptionListener to be removed
     */ 
    public void removeExceptionListener(ExceptionListener l) {
        listenerList.remove(ExceptionListener.class, l);
    }
    
    /**
     * Notifies all registered ExceptionListeners.
     * Code is similar to the example in the Java 2 API
     * documentation for the EventListenerList class.
     */    
    public void fireExceptionGenerated(ExceptionEvent ex) {
        
        // Guaranteed to return a non-null array        
        Object[] listeners = listenerList.getListenerList();

        // Process the listeners last to first, notifying
        // those that are interested in this event.
        // I have no idea why the for loop counts backwards by 2
        // and the array indices are the way they are.        
        for (int i = listeners.length - 2; i >= 0; i -= 2) {
            if (listeners[i] == ExceptionListener.class) {
                ((ExceptionListener) listeners[i + 1]).exceptionGenerated(ex);
            }
        }
    }
}
